package com.googlecode.garbagecan.dbtest.benchmark;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class BenchmarkJobForPostgreSQL implements BenchmarkJob {
	
	private static final int BATCH_SIZE = 10000;
	
	private static final int BATCH_COUNT = 100;
	
	public void setUp() throws BenchmarkException {
		String[] sqls = new String[] {
			"CREATE TABLE T1(ID VARCHAR(128), C1 VARCHAR(256), C2 VARCHAR(256), C3 VARCHAR(256), C4 VARCHAR(256), C5 VARCHAR(256), C6 VARCHAR(256), C7 VARCHAR(256), C8 VARCHAR(256), C9 VARCHAR(256), C10 VARCHAR(256))", 
			"CREATE TABLE T2(ID VARCHAR(128), C1 VARCHAR(256), C2 VARCHAR(256), C3 VARCHAR(256), C4 VARCHAR(256), C5 VARCHAR(256), C6 VARCHAR(256), C7 VARCHAR(256), C8 VARCHAR(256), C9 VARCHAR(256), C10 VARCHAR(256))", 
			"CREATE TABLE T3(ID VARCHAR(128), C1 VARCHAR(256), C2 VARCHAR(256), C3 VARCHAR(256), C4 VARCHAR(256), C5 VARCHAR(256), C6 VARCHAR(256), C7 VARCHAR(256), C8 VARCHAR(256), C9 VARCHAR(256), C10 VARCHAR(256))" 
		};
		
		try {
			Connection conn = getConnection();
			for (String sql : sqls) {
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.executeUpdate();
				ps.close();
			}
			conn.commit();
			conn.close();
		} catch(Exception e) {
			throw new BenchmarkException(e.getMessage(), e);
		}
	}

	public void run() throws BenchmarkException {
		try {
			testInsert();
		} catch(Exception e) {
			throw new BenchmarkException(e.getMessage(), e);
		}
	}
	
	public void tearDown() throws BenchmarkException {
		String[] sqls = new String[] {
			"DROP TABLE T1", 
			"DROP TABLE T2", 
			"DROP TABLE T3" 
		};
		
		try {
			Connection conn = getConnection();
			for (String sql : sqls) {
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.executeUpdate();
				ps.close();
			}
			conn.close();
		} catch(Exception e) {
			throw new BenchmarkException(e.getMessage(), e);
		}
	}
	
	private void testInsert() throws SQLException, ClassNotFoundException {
		long start = System.currentTimeMillis();
		Connection conn = getConnection();
		for (int i = 0; i < 10; i++) {
			testInsert(conn);
		}
		conn.close();
		long stop = System.currentTimeMillis();
		System.out.println("Time to insert " + BATCH_SIZE + " * " + BATCH_COUNT + " redords : " + (stop - start));
	}
	
	private void testInsert(Connection conn) throws SQLException {
		long start = System.currentTimeMillis();
		String sql = "INSERT INTO T1(ID, C1, C2, C3, C4, C5, C6, C7, C8, C9, C10)values(?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		for (int i = 0; i < BATCH_SIZE; i++) {
			for (int colIdx = 1; colIdx <= 11; colIdx ++) {
				ps.setObject(colIdx, "12345678901234567890_" + colIdx);
			}
			ps.addBatch();
		}
		ps.executeBatch();
		ps.clearBatch();
		ps.close();
		conn.commit();
		
		long stop = System.currentTimeMillis();
		System.out.println("Time to insert " + BATCH_SIZE + " redords : " + (stop - start));
	}
	
	private Connection getConnection() throws ClassNotFoundException, SQLException {
		Class.forName("org.postgresql.Driver");
		String url = "jdbc:postgresql://172.17.18.211/fkong";
		Connection conn = DriverManager.getConnection(url, "fkong", "fkong");
		conn.setAutoCommit(false);
		checkConnection(conn);
		return conn;
	}
	
	private void checkConnection(Connection conn) throws SQLException {
		String sql = " select version()";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			System.out.println(rs.getString(1));
		}
		rs.close();
		ps.close();
	}
}
